from openpyxl import load_workbook
from data.ecxel_column import ExcelColumn
from common.read_json import read_json
from common.read_yaml import read_yaml
from common.read_pathini import ReadIni

class ReadExcel:
    def __init__(self):
        # 获取excel文件路径
        excel_path = ReadIni().get_excel_path()
        # 获取params文件路径
        self.params_json = ReadIni().get_params_path()
        # 获取sql.yaml文件路径
        self.sql_ini = ReadIni().get_yaml_path()
        wb = load_workbook(excel_path)
        self.ws = wb[ReadIni().get_sheet_name()]

    # 声明一个方法来获取单元格的值
    def get_cell_value(self, column, row):
        return self.ws[column + str(row)].value

    # 获取用例编号
    def get_case_id(self, row):
        return self.get_cell_value(ExcelColumn.CASE_CASEID, row)

    # 获取用例标题
    def get_case_name(self, row):
        return self.get_cell_value(ExcelColumn.CASE_NAME, row)

    # 获取请求URL
    def get_case_url(self, row):
        return self.get_cell_value(ExcelColumn.CASE_URL, row)

    # 获取请求方法
    def get_case_method(self, row):
        return self.get_cell_value(ExcelColumn.CASE_METHOD, row)

    # 获取前置sql关键字
    def get_case_beforesql_key(self, row):
        return self.get_cell_value(ExcelColumn.CASE_BEFORESQL, row)

    # 获取前置sql语句
    def get_case_beforesql_value(self, row):
        if self.get_case_beforesql_key(row):
            return read_yaml(self.sql_ini)[self.get_case_beforesql_key(row)]

    # 获取前置用例ID
    def get_case_beforecaseid(self, row):
        return self.get_cell_value(ExcelColumn.CASE_BEFORECASEID, row)

    # 获取依赖字段
    def get_case_dependent(self, row):
        return self.get_cell_value(ExcelColumn.CASE_DEPENDENT, row)

    # 获取正则表达式
    def get_case_regular(self, row):
        return self.get_cell_value(ExcelColumn.CASE_REGULAR, row)

    # 获取请求参数的关键字
    def get_case_params_key(self, row):
        return self.get_cell_value(ExcelColumn.CASE_PARAMS, row)
    # 获取请求参数
    def get_case_params_value(self, row):
        if self.get_case_params_key(row):
            return read_json(self.params_json)[self.get_case_params_key(row)]

    # 获取请求参数类型
    def get_case_paramstype(self, row):
        return self.get_cell_value(ExcelColumn.CASE_PARAMSTYPE, row)

    # 获取请求头关键字
    def get_case_headers_key(self, row):
        return self.get_cell_value(ExcelColumn.CASE_HEADERS, row)
    # 获取请求头
    def get_case_headers_value(self, row):
        if self.get_case_headers_key(row):
            return read_json(self.params_json)[self.get_case_headers_key(row)]

    # 获取身份token
    def get_case_identity(self,row):
        return self.get_cell_value(ExcelColumn.CASE_IDENTITY, row)

    # 获取预期结果的关键字
    def get_case_expect(self, row):
        return self.get_cell_value(ExcelColumn.CASE_EXPECT, row)

    # 获取该用例是否执行
    def get_case_execute(self, row):
        return self.get_cell_value(ExcelColumn.CASE_EXECUTE, row)

    # 获取表的行数
    def get_row_count(self):
        return self.ws.max_row
    # 获取表的列数
    def get_column_count(self):
        return self.ws.max_column

if __name__ == '__main__':
    print(ReadExcel().get_row_count(), ReadExcel().get_column_count())
    for row in range(2, 155):
        print(
            ReadExcel().get_case_id(row),
            ReadExcel().get_case_name(row),
            ReadExcel().get_case_url(row),
            ReadExcel().get_case_method(row),
            ReadExcel().get_case_beforesql_value(row),
            ReadExcel().get_case_beforecaseid(row),
            ReadExcel().get_case_dependent(row),
            ReadExcel().get_case_regular(row),
            ReadExcel().get_case_params_value(row),
            ReadExcel().get_case_paramstype(row),
            ReadExcel().get_case_headers_value(row),
            ReadExcel().get_case_identity(row),
            ReadExcel().get_case_expect(row),
            ReadExcel().get_case_execute(row)
        )